﻿using System;
using System.Data;
using System.Data.Common;

/// <summary>
/// Contains generic data access functionality to be accessed from the business tier
/// </summary>
public static class GenericDataAccess
{
	static GenericDataAccess()
	{
		//
		// TODO: Add constructor logic here
		//
	}

	/// <summary>
	/// Execute a command and returns the results as a DataTable object
	/// </summary>
	/// <param name="cmd">SQL Command</param>
	/// <returns>DataTable Object</returns>
	public static DataTable ExecuteSelectCommand(DbCommand cmd)
	{
		DataTable table;
		try
		{
			cmd.Connection.Open();
			DbDataReader reader = cmd.ExecuteReader();
			table = new DataTable();
			table.Load(reader);
			reader.Close();
		}
		catch (Exception error)
		{
			//Utilities.LogError(error);
			throw error;
		}
		finally
		{
			cmd.Connection.Close();
		}

		return table;
	}

	/// <summary>
	/// Execute a insert command
	/// </summary>
	/// <param name="cmd">SQL Command</param>
	public static bool ExecuteCommand(DbCommand cmd)
	{
		try
		{
			cmd.Connection.Open();
			if (cmd.ExecuteNonQuery() == 1)
			{
				return true;
			}
			return false;
		}
		catch (Exception error)
		{
			Utilities.LogError(error);
			throw;
		}
		finally
		{
			cmd.Connection.Close();
		}
	}

	/// <summary>
	/// Create and prepare a new DbCommand object on a new connection
	/// </summary>
	/// <returns>SQL Command object as store procedure only</returns>
	public static DbCommand CreateCommand()
	{
		// Obtain the database provider name
		string dataProviderName = ElegantFoodConfiguration.DbProviderName;

		// Obtain the database connection string
		string connectionString = ElegantFoodConfiguration.DbConnectionString;

		// Create new data provider factory
		DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);

		// Obtain a database-specific connection object
		DbConnection conn = factory.CreateConnection();

		// Set the connection string
		conn.ConnectionString = connectionString;

		// Create database-specific command object
		DbCommand cmd = conn.CreateCommand();

		// Set the command type for store procedure
		cmd.CommandType = CommandType.StoredProcedure;

		// Return command
		return cmd;
	}

	// execute an update, delete, or insert command 
	// and return the number of affected rows
	public static int ExecuteNonQuery(DbCommand command)
	{
		// The number of affected rows 
		int affectedRows = -1;
		// Execute the command making sure the connection gets closed in the end
		try
		{
			// Open the connection of the command
			command.Connection.Open();
			// Execute the command and get the number of affected rows
			affectedRows = command.ExecuteNonQuery();
		}
		catch (Exception ex)
		{
			// Log eventual errors and rethrow them
			Utilities.LogError(ex);
			throw;
		}
		finally
		{
			// Close the connection
			command.Connection.Close();
		}
		// return the number of affected rows
		return affectedRows;
	}

	// execute a select command and return a single result as a string
	public static string ExecuteScalar(DbCommand command)
	{
		// The value to be returned 
		string value = "";
		// Execute the command making sure the connection gets closed in the end
		try
		{

			// Open the connection of the command
			command.Connection.Open();
			// Execute the command and get the number of affected rows
			value = command.ExecuteScalar().ToString();
		}
		catch (Exception ex)
		{
			// Log eventual errors and rethrow them
			Utilities.LogError(ex);
			throw;
		}
		finally
		{
			// Close the connection
			command.Connection.Close();
		}
		// return the result
		return value;
	}
}